package ls.impl;

import ls.dao.StudentDAO;
import ls.entity.Student;
import Connection.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl extends DAOBase implements StudentDAO {
    private static final String STUDENT_INSERT_SQL =
            "INSERT INTO student VALUES(?,?,?,?,?,?,?,?,?,?,?,?) ";
    @Override
    public void addStudent(Student student) {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(STUDENT_INSERT_SQL);
            ps.setString(1,student.getStudent_id());
            ps.setString(2,student.getStudent_pwd());
            ps.setString(3,student.getStudent_name());
            ps.setString(4,student.getStudent_major());
            ps.setString(5,student.getStudent_education());
            ps.setString(6,student.getFirstVol());
            ps.setString(7,student.getSecondVol());
            ps.setString(8,student.getState());
            ps.setString(9,student.getCourse());
            ps.setString(10,student.getTeacher());
            ps.setInt(11,student.getCommunicate_num());
            ps.setInt(12,student.getProject_num());
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    private static final String STUDENT_UPDATE_SQL =  "UPDATE student SET firstvol=?,secondvol=? WHERE student_id=?";
    @Override
    public void updateStudent(Student student) {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(STUDENT_UPDATE_SQL);
            ps.setString(1,student.getFirstVol());
            ps.setString(2,student.getSecondVol());
            ps.setString(3,student.getStudent_id());
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    private static final String STUDENT_SELECT_SQL = "SELECT * FROM student WHERE student_id=?";
    @Override
    public Student getStudent(String student_id) {
        Student student = null;
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(STUDENT_SELECT_SQL);
            preparedStatement.setString(1,student_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next())
            {
                student = new Student();
                student.setStudent_id(resultSet.getString("student_id"));
                student.setStudent_pwd(resultSet.getString("student_pwd"));
                student.setStudent_name(resultSet.getString("student_name"));
                student.setStudent_major(resultSet.getString("student_major"));
                student.setStudent_education(resultSet.getString("student_education"));
                student.setFirstVol(resultSet.getString("firstvol"));
                student.setSecondVol(resultSet.getString("secondvol"));
                student.setCourse(resultSet.getString("course"));
                student.setState(resultSet.getString("state"));
                student.setTeacher(resultSet.getString("teacher"));
                student.setCommunicate_num(resultSet.getInt("communicate_num"));
            }
            resultSet.close();
            preparedStatement.close();
        }catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            }catch (Exception e)
            {
                e.printStackTrace();
            }
        }
        return student;
    }
    private static final String STUDENT_SELECT_SQL2 = "SELECT * FROM student WHERE (firstvol=? or secondvol=?) and state=?";
    @Override
    public List<Student> choose(String course_name) {
        List<Student> studentList = new ArrayList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(STUDENT_SELECT_SQL2);
            ps.setString(1,course_name);
            ps.setString(2,course_name);
            ps.setString(3,"未助教");
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String student_id = rs.getString("student_id");
                String student_pwd = rs.getString("student_pwd");
                String student_name = rs.getString("student_name");
                String student_major = rs.getString("student_major");
                String se = rs.getString("student_education");
                String fv = rs.getString("firstvol");
                String sv = rs.getString("secondvol");
                String state = rs.getString("state");
                String course = rs.getString("course");
                String teacher = rs.getString("teacher");
                int communicate_num = rs.getInt("communicate_num");
                int project_num = rs.getInt("project_num");
                Student student = new Student(student_id,student_pwd,student_name,student_major,se,fv,sv,
                        state,course,teacher,communicate_num,project_num);
                studentList.add(student);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  studentList;
    }

    private static final String STUDENT_UPDATE_SQL2 =  "UPDATE student SET state=?,course=? WHERE student_id=?";
    @Override
    public void updateStudentState(Student student) {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(STUDENT_UPDATE_SQL2);
            ps.setString(1,student.getState());
            ps.setString(2,student.getCourse());
            ps.setString(3,student.getStudent_id());
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static final String STUDENT_UPDATE_SQL3 =  "UPDATE student SET state=?,course=?,firstvol=?,secondvol=?" +
            " WHERE student_id=?";
    @Override
    public void updateDefeat(Student student) {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(STUDENT_UPDATE_SQL3);
            ps.setString(1,student.getState());
            ps.setString(2,student.getCourse());
            ps.setString(3,student.getFirstVol());
            ps.setString(4,student.getSecondVol());
            ps.setString(5,student.getStudent_id());
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    private static final String STUDENT_SELECT_SQL3 = "SELECT * FROM student";
    @Override
    public List<Student> getAllStudent() {
        List<Student> studentList = new ArrayList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(STUDENT_SELECT_SQL3);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String student_id = rs.getString("student_id");
                String student_pwd = rs.getString("student_pwd");
                String student_name = rs.getString("student_name");
                String student_major = rs.getString("student_major");
                String se = rs.getString("student_education");
                String fv = rs.getString("firstvol");
                String sv = rs.getString("secondvol");
                String state = rs.getString("state");
                String course = rs.getString("course");
                String teacher = rs.getString("teacher");
                int communicate_num = rs.getInt("communicate_num");
                int project_num = rs.getInt("project_num");
                Student student = new Student(student_id,student_pwd,student_name,student_major,se,fv,sv,
                        state,course,teacher,communicate_num,project_num);
                studentList.add(student);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  studentList;
    }
}